/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cmu.lti.sepia.infra.system.model.FilePermission;
import edu.cmu.lti.sepia.infra.system.model.UploadedFile;

/**
 * 
 * Manages Uploaded File object in a DB.
 * 
 * @see UploadedFile
 * @author Hideki Shima
 *
 */
public class UploadedFileDAO {

  public static void addFile( SQL sql, UploadedFile file ) throws SQLException  {
    String query = Query.generate( Query.ADD_FILE, "" );
    
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    
    ps.setInt(1, file.getUserId());
    ps.setInt(2, file.getTaskId()!=-1?file.getTaskId():0);
    ps.setString(3, file.getDate());
    ps.setString(4, file.getFileName());
    ps.setString(5, file.getFilePath());
    ps.setString(6, file.getType());
    ps.setString(7, file.getDescription());
    ps.setInt(8, file.getSize());
    sql.execute(ps);
    ps.close();
    
    //This rarely does real job
    FilePermissisonDAO.addFilePermissison(sql, file.getFileId(), file.getPermission());
  }
  
  public static void updateFile( SQL sql, UploadedFile file ) throws SQLException  {
    String query = Query.generate( Query.UPDATE_FILE, "" );

    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, file.getUserId());
    ps.setInt(2, file.getTaskId()!=-1?file.getTaskId():0);
    ps.setString(3, file.getDate());
    ps.setString(4, file.getDescription());
    ps.setInt(5, file.getSize());
    ps.setInt(6, file.getFileId());
    sql.execute(ps);
    ps.close();
    
    FilePermissisonDAO.updateFilePermissison(sql, file.getFileId(), file.getPermission());
  }
  
  public static void updateUser( SQL sql, int fileId, int userId ) throws SQLException {
    String query = Query.generate( Query.UPDATE_FILE_OWNER, "" );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, userId);
    ps.setInt(2, fileId);
    sql.execute(ps);
    ps.close();
  }
   
  public static void deleteFile( SQL sql, int fileId ) throws SQLException {
    String query = Query.generate( Query.DELETE_FILE, "" );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, fileId);
    sql.execute(ps);
    ps.close();
    
    FilePermissisonDAO.deleteFilePermissison(sql, fileId);
  }
  
  public static UploadedFile findById( SQL sql, int fileId ) 
    throws SQLException {
    String query = Query.generate( Query.FIND_FILE, "" );
    
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, fileId);
    ResultSet rs = null;
    UploadedFile result = null;
    
    rs = sql.executeSelect(ps);
    if ( rs.next() ) result = rsToObject(rs);

    rs.close();
    ps.close();
    
    // load permissions
    FilePermission permission = FilePermissisonDAO.findByFileId(sql, fileId);
    result.setPermission(permission);
    
    return result;
  }
  
  public static List<UploadedFile> getFiles( SQL sql ) 
  throws SQLException {
    String query = Query.generate( Query.GET_FILES, "" );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    List<UploadedFile> result = new ArrayList<UploadedFile>();
    rs = sql.executeSelect(ps);
    while ( rs.next() ) {
      result.add( rsToObject(rs) );
    }
    rs.close();
    ps.close();
    
    // load permissions
    for (UploadedFile file : result) {
      FilePermission permission = FilePermissisonDAO.findByFileId(sql, file.getFileId());
      file.setPermission(permission);
    }
    
    return result;
  }
  
  public static List<UploadedFile> findByUserAndTaskId( SQL sql, int userId, int taskId ) 
    throws SQLException {
    String query;
    if (taskId==-1) {
      query = Query.generate( Query.FIND_FILES_BY_USER+" ORDER BY date", "" );
    } else {
      query = Query.generate( Query.FIND_FILES_BY_USERTASK+" ORDER BY date", "" );
    }
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, userId);
    if (taskId!=-1) ps.setInt(2, taskId);
    ResultSet rs = null;
    List<UploadedFile> result = new ArrayList<UploadedFile>();
    rs = sql.executeSelect(ps);
    while ( rs.next() ) {
      result.add( rsToObject(rs) );
    }
    rs.close();
    ps.close();
    
    // load permissions
    for (UploadedFile file : result) {
      FilePermission permission = FilePermissisonDAO.findByFileId(sql, file.getFileId());
      file.setPermission(permission);
    }
    
    return result;
  }
  
  public static List<UploadedFile> findByUserId( SQL sql, int userId ) 
  throws SQLException {
    return findByUserAndTaskId(sql, userId, -1);
  }
  
  public static UploadedFile findByTypeAndName( SQL sql, String type, String fileName ) 
  throws SQLException {
    String query = Query.generate( Query.FIND_FILE_BY_NAME, "" );
    
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setString(1, type);
    ps.setString(2, fileName);
    ResultSet rs = null;
    UploadedFile file = null;
    
    rs = sql.executeSelect(ps);
    if ( rs.next() ) file = rsToObject(rs);
    
    rs.close();
    ps.close();
    
    if (file!=null) {
      // load permissions
      FilePermission permission = FilePermissisonDAO.findByFileId(sql, file.getFileId());
      file.setPermission(permission);
    }
    
    return file;
  }
  
  private static UploadedFile rsToObject( ResultSet rs ) throws SQLException {
    UploadedFile file = new UploadedFile( rs.getInt(1), rs.getInt(2),  rs.getInt(3), 
        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7),
        rs.getString(8), rs.getInt(9), null);
    return file;
  }
}
